/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package gestioninscription;
import gestionrentabilite.GestionRenta;
import gestionsession.GestionSession;
import java.sql.*;
import sql.*;
/**
 *
 * @author Administrateur
 */
public class FenInscription extends javax.swing.JFrame {

    /**
     * Creates new form FenInscription
     */
    Connection conn;
    Statement stmt1, stmt2;
    
    public FenInscription()
    {
        initComponents();
        jButton1.setVisible(false); // On rend le bouton inscription non visible
        jTable1.selectAll();
        jTable1.setDefaultRenderer(Object.class, new Couleur()); // Pour mettre les sessions déficitaires en rouge
    }

    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jMenuItem1 = new javax.swing.JMenuItem();
        jLabel1 = new javax.swing.JLabel();
        jLabel2 = new javax.swing.JLabel();
        jComboBox1 = new javax.swing.JComboBox();
        jLabel3 = new javax.swing.JLabel();
        jScrollPane1 = new javax.swing.JScrollPane();
        jTable1 = new javax.swing.JTable();
        jButton1 = new javax.swing.JButton();
        jLabel5 = new javax.swing.JLabel();
        jMenu = new javax.swing.JMenuBar();
        jMenu1 = new javax.swing.JMenu();
        jMenuRenta = new javax.swing.JMenuItem();
        jMenu2 = new javax.swing.JMenu();
        jMenuItem2 = new javax.swing.JMenuItem();

        jMenuItem1.setText("jMenuItem1");

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        setTitle("Inscription des clients aux différentes sessions");
        setCursor(new java.awt.Cursor(java.awt.Cursor.DEFAULT_CURSOR));
        addWindowListener(new java.awt.event.WindowAdapter() {
            public void windowActivated(java.awt.event.WindowEvent evt) {
                formWindowActivated(evt);
            }
            public void windowOpened(java.awt.event.WindowEvent evt) {
                formWindowOpened(evt);
            }
        });

        jLabel1.setFont(new java.awt.Font("Tahoma", 0, 30)); // NOI18N
        jLabel1.setHorizontalAlignment(javax.swing.SwingConstants.CENTER);
        jLabel1.setText("GESTION DES INSCRIPTIONS");
        jLabel1.setCursor(new java.awt.Cursor(java.awt.Cursor.DEFAULT_CURSOR));

        jLabel2.setText("Matricule : ");

        jComboBox1.setName("cmbMat"); // NOI18N
        jComboBox1.addItemListener(new java.awt.event.ItemListener() {
            public void itemStateChanged(java.awt.event.ItemEvent evt) {
                jComboBox1ItemStateChanged(evt);
            }
        });

        jLabel3.setText(" ");
        jLabel3.setName("lblNomPren"); // NOI18N

        jTable1.setModel(new ModeleJTableListeSession());
        jTable1.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                jTable1MouseClicked(evt);
            }
        });
        jTable1.addInputMethodListener(new java.awt.event.InputMethodListener() {
            public void caretPositionChanged(java.awt.event.InputMethodEvent evt) {
            }
            public void inputMethodTextChanged(java.awt.event.InputMethodEvent evt) {
                jTable1InputMethodTextChanged(evt);
            }
        });
        jScrollPane1.setViewportView(jTable1);

        jButton1.setText("Inscription");
        jButton1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton1ActionPerformed(evt);
            }
        });

        jLabel5.setText("Sélection : ");

        jMenu1.setText("Rentabilitée");
        jMenu1.setActionCommand("Rentabilité");

        jMenuRenta.setText("Liste des Sessions");
        jMenuRenta.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jMenuRentaActionPerformed(evt);
            }
        });
        jMenu1.add(jMenuRenta);

        jMenu.add(jMenu1);

        jMenu2.setText("Sessions");

        jMenuItem2.setText("Gestion des sessions");
        jMenuItem2.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                jMenuItem2MouseClicked(evt);
            }
        });
        jMenuItem2.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jMenuItem2ActionPerformed(evt);
            }
        });
        jMenu2.add(jMenuItem2);

        jMenu.add(jMenu2);

        setJMenuBar(jMenu);

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jLabel3, javax.swing.GroupLayout.PREFERRED_SIZE, 278, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 850, Short.MAX_VALUE)
                    .addComponent(jButton1, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)))
            .addGroup(layout.createSequentialGroup()
                .addGap(19, 19, 19)
                .addComponent(jLabel2, javax.swing.GroupLayout.PREFERRED_SIZE, 62, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jComboBox1, javax.swing.GroupLayout.PREFERRED_SIZE, 128, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(109, 109, 109)
                .addComponent(jLabel5, javax.swing.GroupLayout.PREFERRED_SIZE, 130, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(0, 0, Short.MAX_VALUE))
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jLabel1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addContainerGap())
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addComponent(jLabel1, javax.swing.GroupLayout.PREFERRED_SIZE, 23, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(18, 18, 18)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jComboBox1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel2, javax.swing.GroupLayout.PREFERRED_SIZE, 23, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel5, javax.swing.GroupLayout.PREFERRED_SIZE, 23, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jLabel3, javax.swing.GroupLayout.PREFERRED_SIZE, 22, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 153, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jButton1, javax.swing.GroupLayout.PREFERRED_SIZE, 35, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap(59, Short.MAX_VALUE))
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void formWindowOpened(java.awt.event.WindowEvent evt) {//GEN-FIRST:event_formWindowOpened
            
            try
            {
                // On prévoit 2 connexions à la base
                stmt1 = GestionBdd.connexionBdd(GestionBdd.TYPE_MYSQL, "formarmor","localhost", "root","");
                stmt2 = GestionBdd.connexionBdd(GestionBdd.TYPE_MYSQL, "formarmor","localhost", "root","");
                // Liste des clients qui "ont un plan de formation"
                String req = "select distinct c.matricule from client c, plan_formation p "
                 + "where c.matricule = p.matricule order by c.matricule";
                ResultSet rs = GestionBdd.envoiRequeteLMD(stmt1,req);
                jComboBox1.addItem("---");
                while (rs.next())
                {
                    jComboBox1.addItem(rs.getString(1));
                }
            }
            catch (SQLException se)
            {
                System.out.println("Erreur SQL1 : " + se.getMessage());
            }
    }//GEN-LAST:event_formWindowOpened
    
    private void jComboBox1ItemStateChanged(java.awt.event.ItemEvent evt) {//GEN-FIRST:event_jComboBox1ItemStateChanged
          // Affichage du nom prénom et de la liste des sessions autorisées
        if (evt.getStateChange() != 1) // Pour éviter le déclenchement sur la création de la fenêtre
        {
            renseigne();
		
        }
    }//GEN-LAST:event_jComboBox1ItemStateChanged

    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
        FenConfirmationInscription f2 = new FenConfirmationInscription(this, true);
        f2.init(jComboBox1.getSelectedItem(), jTable1.getValueAt(jTable1.getSelectedRow(),0));
        f2.setSize(400,300);
        f2.setVisible(true);
    }//GEN-LAST:event_jButton1ActionPerformed

    private void jTable1InputMethodTextChanged(java.awt.event.InputMethodEvent evt) {//GEN-FIRST:event_jTable1InputMethodTextChanged
        //
    }//GEN-LAST:event_jTable1InputMethodTextChanged

    private void jTable1MouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_jTable1MouseClicked
        if (jComboBox1.getSelectedIndex()>0 && jTable1.getValueAt(jTable1.getSelectedRow(), 0) != null)
        {
            jLabel5.setText("Session n° :"+jTable1.getValueAt(jTable1.getSelectedRow(), 0).toString());
            int pos1 = jLabel3.getText().indexOf(":");
            String nomPrenom = jLabel3.getText().substring(pos1 + 1);
            jButton1.setText("Inscription à la session numéro " + jTable1.getValueAt(jTable1.getSelectedRow(), 0) + " pour " + nomPrenom);
            jButton1.setVisible(true);
        }
        else
        {
            jButton1.setVisible(false);
            jLabel5.setText("Aucune sélection");
        }
    }//GEN-LAST:event_jTable1MouseClicked

    private void formWindowActivated(java.awt.event.WindowEvent evt) {//GEN-FIRST:event_formWindowActivated
        renseigne();
    }//GEN-LAST:event_formWindowActivated

    private void jMenuRentaActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jMenuRentaActionPerformed
        gestionrentabilite.GestionRenta rentabilite = new GestionRenta();
                
    }//GEN-LAST:event_jMenuRentaActionPerformed

    private void jMenuItem2MouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_jMenuItem2MouseClicked
        
    }//GEN-LAST:event_jMenuItem2MouseClicked

    private void jMenuItem2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jMenuItem2ActionPerformed
        gestionsession.GestionSession session = new GestionSession();
    }//GEN-LAST:event_jMenuItem2ActionPerformed
    private void renseigne()
    {
        try
        {
        // On vide le JTable
            int j, k;
            String req;
            for(int i=0;i<jTable1.getRowCount();i++)
            {
                for (j=0;j<jTable1.getColumnCount();j++)
		{
                    jTable1.setValueAt(null,i,j);
		}
            }
            if (jComboBox1.getSelectedIndex()==0 || jComboBox1.getSelectedItem()== null) // Pas de client de choisi
            {
                jLabel3.setText("ENSEMBLE DES SESSIONS");
                // Toutes les sessions
                req = "select null, s.numero, s.libelleform, s.niveauform, s.datedebut, duree, nb_places, nb_inscrits, coutrevient ";
                req += "from session_form s, formation f ";
                req += "where s.libelleform = f.libelle and s.niveauform = f.niveau ";
                // et date supérieure à la date du jour
                jButton1.setVisible(false); // On rend le bouton inscription non visible
            }
            else
            {
                // Sélection des sessions "autorisées"
                req = "select c.nom, s.numero, s.libelleform, s.niveauform, s.datedebut, duree, nb_places, nb_inscrits, coutrevient ";
                req += "from session_form s, client c, plan_formation p, formation f ";
                req += "where c.matricule = '" + jComboBox1.getSelectedItem() + "' ";
                req += "and p.matricule = c.matricule and nb_places > nb_inscrits ";
                req += "and p.libelle_form = f.libelle and p.niveau = f.niveau ";
                req += "and s.libelleform = f.libelle and s.niveauform = f.niveau ";
                // et date supérieure à la date du jour
                req += "and close = 0 and effectue = 0 and s.numero Not In ";
                req += "(Select num_session From inscription Where matricule = '" + jComboBox1.getSelectedItem() + "')";
                if (jTable1.getValueAt(jTable1.getSelectedRow(), 0) != null) //Si la cellule sélectionnée est vide
                {
                    jButton1.setVisible(false); // On rend le bouton inscription non visible
                }
            }
            stmt1 = GestionBdd.connexionBdd(GestionBdd.TYPE_MYSQL, "formarmor","localhost", "root","");
            ResultSet rs2 = GestionBdd.envoiRequeteLMD(stmt1, req);
            ResultSet rs3 = null;
            
            k = 0;
            try
            {
                while(rs2.next())
                {
                    if (k==0 && jComboBox1.getSelectedIndex()!=0)
                    {
                        jLabel3.setText("Sessions autorisées pour : " + rs2.getString(1));
                    }
                    // On calcule la marge et on renseigne la dernière colonne(7ème) du jTable
                    req = "Select sum(taux_horaire) as revenu_session ";
                    req += "from statut st, session_form s, client c, inscription i ";
                    req += "where s.numero = i.num_session and c.matricule = i.matricule ";
                    req += "and c.typestatut = st.type and s.numero = " + rs2.getInt(2);
                    stmt2 = GestionBdd.connexionBdd(GestionBdd.TYPE_MYSQL, "formarmor","localhost", "root","");
                    rs3 = GestionBdd.envoiRequeteLMD(stmt2, req);
                    rs3.first();
                    jTable1.setValueAt((rs3.getFloat(1) - rs2.getFloat(9)), k, 7);
                    // On renseigne le reste du jTable
                    for (j=0;j<(jTable1.getColumnCount() -1);j++)
                    {
                        jTable1.setValueAt(rs2.getObject(j+2), k, j);
                    }
                    k++;
                }
                rs2.close();
            }
            catch (SQLException sqle)
            {
                System.out.print("Erreur SQL : " + sqle.getMessage());
            }
        }
        catch(NullPointerException npe)
        {
            System.out.println("Erreur : " + npe.getMessage());
        }
    }
    /**
     * @param args the command line arguments
     */
    
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton jButton1;
    private javax.swing.JComboBox jComboBox1;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel5;
    private javax.swing.JMenuBar jMenu;
    private javax.swing.JMenu jMenu1;
    private javax.swing.JMenu jMenu2;
    private javax.swing.JMenuItem jMenuItem1;
    private javax.swing.JMenuItem jMenuItem2;
    private javax.swing.JMenuItem jMenuRenta;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JTable jTable1;
    // End of variables declaration//GEN-END:variables
}
